-- configure blocked process threshold
EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXECUTE sp_configure 'blocked process threshold', 30;
RECONFIGURE;
GO
EXECUTE sp_configure 'show advanced options', 0;
RECONFIGURE;
GO

-- extended event session to find blocking queries
IF EXISTS(SELECT 1 FROM sys.server_event_sessions WHERE name = N'Blocking')
	DROP EVENT SESSION Blocking ON SERVER;
GO

-- create event session
CREATE EVENT SESSION Blocking ON SERVER
ADD EVENT sqlserver.blocked_process_report
ADD TARGET package0.event_file
(
	SET FILENAME = N'C:\Temp\Blocking.xel',
		MAX_FILE_SIZE = 200, MAX_ROLLOVER_FILES = 5
);
GO

ALTER EVENT SESSION Blocking ON SERVER STATE = START;
GO

-- monitor the workload

-- stop the event session
ALTER EVENT SESSION Blocking ON SERVER STATE = STOP;
GO

-- query target data
SELECT 
    DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), 
        n.value('(event/@timestamp)[1]', 'datetime2')) AS 'timestamp',
    n.value('(event/action[@name="database_id"]/value)[1]', 'int') AS database_id,
    n.value('(event/data[@name="database_name"]/value)[1]', 'nvarchar(128)') AS database_name,
    n.value('(event/data[@name="object_id"]/value)[1]', 'int') AS object_id,
    n.value('(event/data[@name="index_id"]/value)[1]', 'int') AS index_id,
    CAST(n.value('(event/data[@name="duration"]/value)[1]', 'bigint')/1000.0 AS decimal(12,2)) AS duration_ms,
    n.value('(event/data[@name="lock_mode"]/text)[1]', 'nvarchar(10)') AS lock_mode,
    n.value('(event/data[@name="transaction_id"]/value)[1]', 'bigint') AS transaction_id,
    n.value('(event/data[@name="resource_owner_type"]/text)[1]', 'nvarchar(10)') AS resource_owner_type,
    n.query('event/data[@name="blocked_process"]') AS blocked_process_report
FROM (
	SELECT
		CAST(event_data AS XML) AS event_data
	FROM sys.fn_xe_file_target_read_file
	(N'C:\Temp\Blocking*xel', NULL, NULL, NULL)
) AS q(n);
GO

-- cleanup
DROP EVENT SESSION Blocking ON SERVER;
GO

EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXECUTE sp_configure 'blocked process threshold', 0;
RECONFIGURE;
GO
EXECUTE sp_configure 'show advanced options', 0;
RECONFIGURE;
GO